package com.bluesky.light.order;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import com.bluesky.light.product.ProductService;
import com.bluesky.light.user.User;
import com.bluesky.light.util.DB;

/**
 * 為JavaBean部分病人預約的訂單存取數據庫時, 能夠更方便地調用數據庫, 而編寫的API
 * 
 * @author LEMON
 * 
 */
public class OrderDAO {

	/**
	 * 把一個新的訂單存入數據庫
	 * 
	 * @param to
	 * @return
	 */
	public int add(TreatOrder to) {

		int orderId = -1;

		Connection conn = DB.getConn();
		boolean autoCommit = true;
		try {
			autoCommit = conn.getAutoCommit();
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		String sql = "insert into t_treatorder (userid, odate, status, symptom, staffid) values (?, ?, ?, ?, ?)";
		PreparedStatement pstmt = DB.prepare(conn, sql,
				Statement.RETURN_GENERATED_KEYS);

		String sqlDetail = "insert into t_treatitem (productid, unitprice, pcount, orderid) values (?, ?, ?, ?)";
		PreparedStatement pstmtDetail = DB.prepare(conn, sqlDetail);
		ResultSet rsKey = null;

		try {
			pstmt.setInt(1, to.getUser().getId());
			pstmt.setTimestamp(2, new Timestamp(to.getoDate().getTime()));
			pstmt.setString(3, to.getStatus());
			pstmt.setString(4, to.getSymptom());
			pstmt.setInt(5, to.getStaffid());
			pstmt.executeUpdate();
			rsKey = pstmt.getGeneratedKeys();
			rsKey.next();
			orderId = rsKey.getInt(1);

			List<TreatItem> items = to.getItems();
			Iterator<TreatItem> it = items.iterator();

			while (it.hasNext()) {
				TreatItem ti = it.next();
				pstmtDetail.setInt(1, ti.getProduct().getId());
				pstmtDetail.setDouble(2, ti.getProduct().getPrice());
				pstmtDetail.setInt(3, ti.getCount());
				pstmtDetail.setInt(4, orderId);
				pstmtDetail.addBatch();
			}

			pstmtDetail.executeBatch();
			conn.commit();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(autoCommit);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			DB.close(rsKey);
			DB.close(pstmtDetail);
			DB.close(pstmt);
			DB.close(conn);
		}

		return orderId;
	}

	/**
	 * 獲取求診的訂單
	 * 
	 * @return orders
	 */
	public List<TreatOrder> getOrders() {
		Connection conn = null;
		String sql = "select o.id oid, o.odate, o.status, o.symptom, u.name username, s.username staffname "
				+ "from t_treatorder o, t_user u, t_staff s where o.userid = u.id and o.staffid = s.id";
		Statement stmt = null;
		ResultSet rs = null;

		List<TreatOrder> orders = null;

		try {
			conn = DB.getConn();
			stmt = DB.getStatement(conn);
			rs = DB.getResultSet(stmt, sql);

			orders = new ArrayList<TreatOrder>();

			while (rs.next()) {
				TreatOrder to = new TreatOrder();
				to.setId(rs.getInt("oid"));
				to.setoDate(rs.getTimestamp("odate"));
				to.setStatus(rs.getString("status"));
				to.setSymptom(rs.getString("symptom"));
				to.setUsername(rs.getString("username"));
				to.setStaffname(rs.getString("staffname"));

				orders.add(to);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}

		return orders;
	}

	/**
	 * 用戶要預約求診時, 調用這個函數
	 * 
	 * @param u
	 * @param opdate
	 */
	public void createTreatment(User u, Date opdate) {
		Connection conn = DB.getConn();
		String sql = "insert into t_treatorder (userid, odate, status) values (?, ?, ?)";
		PreparedStatement pstmt = DB.prepare(conn, sql);
		try {
			pstmt.setInt(1, u.getId());
			pstmt.setTimestamp(2, new Timestamp(opdate.getTime()));
			pstmt.setString(3, "bok");
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}

	/**
	 * 獲取訂單的總數
	 * 
	 * @param opdate
	 * @return
	 */
	public int getBookCount(Date opdate) {
		int count = -1;

		Connection conn = DB.getConn();
		// String sql = "select count(*) from t_treatorder where odate like '" +
		// String.format("%tF", opdate.getTime()) + "%'";
		String sql = "select count(*) from t_treatorder where date(odate) = '"
				+ String.format("%tF", opdate.getTime()) + "'";
		Statement stmt = DB.getStatement(conn);
		ResultSet rs = null;

		try {
			rs = DB.getResultSet(stmt, sql);
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}
		return count;
	}

	/**
	 * 獲得用戶的個人資料
	 * 
	 * @param userid
	 * @return
	 */
	public User getUser(int userid) {
		User u = new User();
		Connection conn = DB.getConn();
		String sql = "select * from t_user where id = " + userid;
		Statement stmt = DB.getStatement(conn);
		ResultSet rs = null;
		try {
			rs = DB.getResultSet(stmt, sql);
			if (rs.next()) {
				u.setId(rs.getInt("id"));
				u.setUsername(rs.getString("username"));
				u.setPwd(rs.getString("pwd"));
				u.setName(rs.getString("name"));
				u.setIdcard(rs.getString("idcard"));
				u.setSex(rs.getString("sex"));
				u.setBirth(rs.getTimestamp("birth"));
				u.setPhone(rs.getString("phone"));
				u.setAddr(rs.getString("addr"));
				u.setRdate(rs.getTimestamp("rdate"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}
		return u;
	}

	/**
	 * 獲取所有在"預約"狀態下的訂單
	 * 
	 * @param date
	 * @return
	 */
	public List<Integer> getUsersId(Date date) {
		List<Integer> usersid = new ArrayList<Integer>();
		Connection conn = DB.getConn();
		String sql = "select userid from t_treatorder where odate like '"
				+ String.format("%tF", date) + "%' and status = 'bok'";
		Statement stmt = DB.getStatement(conn);
		ResultSet rs = null;
		try {
			rs = DB.getResultSet(stmt, sql);
			while (rs.next()) {
				int userid = rs.getInt("userid");
				usersid.add(userid);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}
		return usersid;
	}

	/**
	 * 更新用戶的"就診狀態"
	 * 
	 * @param to
	 * @return
	 */
	public int update(TreatOrder to) {
		int orderId = -1;

		Connection conn = DB.getConn();
		boolean autoCommit = true;
		try {
			autoCommit = conn.getAutoCommit();
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		String sql = "update t_treatorder set status = ?, symptom = ?, staffid = ? where id = ?";
		PreparedStatement pstmt = DB.prepare(conn, sql);

		String sqlDetail = "insert into t_treatitem (productid, unitprice, pcount, orderid) values (?, ?, ?, ?)";
		PreparedStatement pstmtDetail = DB.prepare(conn, sqlDetail);

		orderId = to.getId();
		try {
			pstmt.setString(1, to.getStatus());
			// System.out.println(""+to.getStatus()+to.getSymptom()+to.getStaffid()+to.getId());
			pstmt.setString(2, to.getSymptom());
			pstmt.setInt(3, to.getStaffid());
			pstmt.setInt(4, to.getId());
			pstmt.executeUpdate();

			List<TreatItem> items = to.getItems();
			Iterator<TreatItem> it = items.iterator();

			while (it.hasNext()) {
				TreatItem ti = it.next();
				pstmtDetail.setInt(1, ti.getProduct().getId());
				pstmtDetail.setDouble(2, ti.getProduct().getPrice());
				pstmtDetail.setInt(3, ti.getCount());
				pstmtDetail.setInt(4, orderId);
				pstmtDetail.addBatch();
			}
			pstmtDetail.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(autoCommit);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			DB.close(pstmtDetail);
			DB.close(pstmt);
			DB.close(conn);
		}

		return orderId;
	}

	/**
	 * 根據日期和"就診狀態"查找訂單
	 * 
	 * @param date
	 * @param status
	 * @return orders
	 */
	public List<TreatOrder> getOrders(Date date, String status) {

		List<TreatOrder> orders = new ArrayList<TreatOrder>();
		Connection conn = DB.getConn();
		// String sql = "select * from t_treatorder where odate like '" +
		// String.format("%tF", date) + "%' and status = '" + status + "'";
		String sql = "select * from t_treatorder where date(odate) = '"
				+ String.format("%tF", date) + "' and status = '" + status
				+ "'";
		Statement stmt = DB.getStatement(conn);
		ResultSet rs = null;

		try {
			rs = DB.getResultSet(stmt, sql);
			while (rs.next()) {
				TreatOrder to = getTreatOrderFromRs(rs);
				orders.add(to);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}
		return orders;
	}

	/**
	 * 從ResultSet中獲得訂單的資料
	 * 
	 * @param rs
	 * @return
	 */
	private TreatOrder getTreatOrderFromRs(ResultSet rs) {
		TreatOrder to = new TreatOrder();
		try {
			to.setId(rs.getInt("id"));
			to.setUserid(rs.getInt("userid"));
			to.setoDate(rs.getTimestamp("odate"));
			to.setStatus(rs.getString("status"));
			to.setSymptom(rs.getString("symptom"));
			to.setStaffid(rs.getInt("staffid"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return to;
	}

	/**
	 * @param u
	 * @param status
	 * @return
	 */
	public TreatOrder getOrder(User u, String status) {
		TreatOrder to = null;

		Connection conn = DB.getConn();
		// System.out.println(conn);
		String sql = "select * from t_treatorder where userid = " + u.getId()
				+ " and status = '" + status + "'";
		Statement stmt = DB.getStatement(conn);
		ResultSet rs = null;

		try {
			rs = DB.getResultSet(stmt, sql);
			if (rs.next()) {
				to = getOrderFromRs(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}
		return to;
	}

	/**
	 * @param rs
	 * @return
	 */
	private TreatOrder getOrderFromRs(ResultSet rs) {
		TreatOrder to = new TreatOrder();
		List<TreatItem> items = new ArrayList<TreatItem>();

		Connection connDetail = null;
		Statement stmtDetail = null;
		ResultSet rsDetail = null;

		try {
			String sqlDetail = "select * from t_treatitem where orderid = "
					+ rs.getInt("id");
			connDetail = DB.getConn();
			stmtDetail = DB.getStatement(connDetail);
			rsDetail = DB.getResultSet(stmtDetail, sqlDetail);

			to.setId(rs.getInt("id"));
			to.setUserid(rs.getInt("userid"));
			to.setoDate(rs.getTimestamp("odate"));
			to.setStatus(rs.getString("status"));
			to.setSymptom(rs.getString("symptom"));
			to.setStaffid(rs.getInt("staffid"));

			while (rsDetail.next()) {
				TreatItem ti = new TreatItem();
				ti.setId(rsDetail.getInt("id"));
				ti.setProductId(rsDetail.getInt("productid"));
				ti.setProduct(ProductService.getInstance().getProductById(
						rsDetail.getInt("productid")));
				ti.setUnitPrice(rsDetail.getDouble("unitprice"));
				ti.setCount(rsDetail.getInt("pcount"));
				ti.setOrderId(rsDetail.getInt("orderid"));
				items.add(ti);
			}

			to.setItems(items);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return to;
	}

	/**
	 * @param treatOrder
	 * @return
	 */
	public List<TreatItem> getItems(TreatOrder treatOrder) {
		List<TreatItem> items = new ArrayList<TreatItem>();

		Connection conn = DB.getConn();
		String sql = "select * from t_treatitem where orderid = "
				+ treatOrder.getId();
		Statement stmt = DB.getStatement(conn);
		ResultSet rs = null;
		try {
			rs = DB.getResultSet(stmt, sql);
			while (rs.next()) {
				TreatItem item = getItemFromRs(rs);
				items.add(item);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}
		return items;
	}

	/**
	 * @param rs
	 * @return
	 */
	private TreatItem getItemFromRs(ResultSet rs) {
		TreatItem item = new TreatItem();
		try {
			item.setId(rs.getInt("id"));
			item.setProductId(rs.getInt("productid"));
			item.setUnitPrice(rs.getDouble("unitprice"));
			item.setCount(rs.getInt("pcount"));
			item.setOrderId(rs.getInt("orderid"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return item;
	}

	/**
	 * @param treatOrderId
	 * @param status
	 */
	public void update(int treatOrderId, String status) {
		Connection conn = DB.getConn();
		String sql = "update t_treatorder set status = '" + status
				+ "' where id = " + treatOrderId;
		Statement stmt = DB.getStatement(conn);
		try {
			DB.excuteUpdate(stmt, sql);
		} finally {
			DB.close(stmt);
			DB.close(conn);
		}

	}

	/**
	 * @param date
	 * @return
	 */
	public List<Double> getFinanicalDates(Date date) {
		Calendar cal = Calendar.getInstance();
		cal.setTime(date);
		int year = cal.get(Calendar.YEAR);
		int month = cal.get(Calendar.MONTH) + 1;
		// String keyword = "" + year + "-" + (month < 10 ? "0"+month : month) +
		// "%";
		Connection conn = DB.getConn();
		/*
		 * String sql =
		 * "select sum(totalprice) totalprice, odate from t_treatorder o " +
		 * "join (select sum(unitprice*pcount) totalprice, orderid from t_treatitem group by orderid) i "
		 * + "on o.id=i.orderid group by day(odate) " + "having year(odate)=" +
		 * year + " and month(odate)=" + month;
		 */
		String sql = "select dodate, sum(totalprice) totalpricebyday from t_treatorder o2 "
				+ "join ( "
				+ "	select totalprice, year(odate) yodate, month(odate) modate, day(odate) dodate, odate "
				+ "	from t_treatorder o "
				+ "	join (select sum(unitprice*pcount) totalprice, orderid from t_treatitem group by orderid) i "
				+ "	on o.id=i.orderid ) pricebyday "
				+ "on day(o2.odate)=pricebyday.dodate " + "group by dodate ";

		Statement stmt = DB.getStatement(conn);
		ResultSet rs = null;

		List<Double> incomes = new ArrayList<Double>();
		int day = 1;
		try {
			rs = DB.getResultSet(stmt, sql);
			while (rs.next()) {
				double income = 0.0;
				// Date odate = rs.getTimestamp("odate");
				// cal.setTime(odate);
				// int oday = cal.get(Calendar.DAY_OF_MONTH);
				int oday = rs.getInt("dodate");
				while (day != oday) {
					incomes.add(income);
					day++;
				}
				income = rs.getDouble("totalpricebyday");
				incomes.add(income);
				day++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}
		cal.setTime(date);
		int monthDays = cal.getActualMaximum(Calendar.DATE);
		while (incomes.size() < monthDays) {
			incomes.add(0.0);
		}

		return incomes;
	}

}
